﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Security.Cryptography;
using Analysis.Entities;
using Dapper;

namespace Analysis.DAL.Repositories
{
    public class UserRepository : BaseRepository
    {
        public IEnumerable<User> FindAll()
        {
            string strSQL = @"  SELECT u.Id, u.UserId, u.Email, u.FirstName, u.LastName, u.Phone,
                                d.DepartId, d.DepartName,
                                convert(bit, 
                                            case when exists(select 1 from [AP] (nolock) where u.Id = AP.Depart1UserId) or
                                                      exists(select 1 from [AP] (nolock) where u.Id = AP.Depart2UserId) or
                                                      exists(select 1 from [AP] (nolock) where u.Id = AP.Depart3UserId) or
                                                      exists(select 1 from [AP] (nolock) where u.Id = AP.Depart4UserId) or
                                                      exists(select 1 from [AP] (nolock) where u.Id = AP.Depart5UserId)
                                                then 0 
                                            else 1 end) as CanDelete
                    FROM [User] (nolock) u left join [Depart] (nolock) as d on u.DepartId = d.DepartId";
            return Connection.Query<User>(strSQL);
        }

        public User Find(int id)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.Email, u.FirstName, u.LastName, u.Phone,
                            u.IsFullAccess, u.IsPageReport, u.IsPageAddress, u.IsPageAssort, u.IsPageMML, u.IsPageSale, u.IsPageUser, u.IsPageSetup,
                            d.DepartId, d.DepartName, am.Password
                    FROM [User] (nolock) u left join [Depart] (nolock) as d on u.DepartId = d.DepartId
                                        left join aspnet_Membership (nolock) as am on u.UserId = am.UserId
                    WHERE u.Id = @userId", new { userId = id }).SingleOrDefault();
        }

        public User Find(Guid userId)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.Email, u.FirstName, u.LastName, u.Phone,
                            u.IsFullAccess, u.IsPageReport, u.IsPageAddress, u.IsPageAssort, u.IsPageMML, u.IsPageSale, u.IsPageUser, u.IsPageSetup,
                            d.DepartId, d.DepartName
                    FROM [User] (nolock) u left join [Depart] (nolock) as d on u.DepartId = d.DepartId
                    WHERE u.UserId = @userId", new { userId = userId }).SingleOrDefault();
        }

        public User Find(string email)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.Email, u.FirstName, u.LastName, u.Phone,
                            u.IsFullAccess, u.IsPageReport, u.IsPageAddress, u.IsPageAssort, u.IsPageMML, u.IsPageSale, u.IsPageUser, u.IsPageSetup,
                            d.DepartId, d.DepartName
                    FROM [User] (nolock) u left join [Depart] (nolock) as d on u.DepartId = d.DepartId
                    WHERE u.Email = @email", new { email = email }).SingleOrDefault();
        }

        public IEnumerable<User> UserComboByDepart(int departId, bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as Id, '' as FirstName, '(Все)' as LastName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as Id, '' as FirstName, '(Выберите персонал)' as LastName, 0 as SortIndex union all ";

            strSQL += @"  SELECT u.Id, u.FirstName, u.LastName, 1 as SortIndex
                    FROM [User] (nolock) u
                    where u.DepartId = @DepartId
                    
                    order by SortIndex, LastName, FirstName";
            return Connection.Query<User>(strSQL, new { DepartId = departId });
        }
        
        public void AddManual(User newUser, string password)
        {
            Connection.Execute(@"spUser_Add @FirstName, @LastName, @Email, 
                            @DepartId, @Phone, @IsFullAccess,
                            @IsPageReport, @IsPageAddress, @IsPageAssort, @IsPageMML, @IsPageSale, @IsPageUser, @IsPageSetup, 
                            @Password, @PasswordSalt",
                new
                {
                    FirstName = newUser.FirstName,
                    LastName = newUser.LastName,
                    Email = newUser.Email,
                    DepartId = newUser.DepartId,
                    Phone = newUser.Phone,
                    IsFullAccess = newUser.IsFullAccess,
                    IsPageReport = newUser.IsPageReport, 
                    IsPageAddress = newUser.IsPageAddress, 
                    IsPageAssort = newUser.IsPageAssort, 
                    IsPageMML = newUser.IsPageMML, 
                    IsPageSale = newUser.IsPageSale, 
                    IsPageUser = newUser.IsPageUser,
                    IsPageSetup = newUser.IsPageSetup,
                    Password = password,
                    PasswordSalt = GeneratePasswordSalt(),

                });
        }

        private string GeneratePasswordSalt()
        {
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            byte[] buff = new byte[32];
            rng.GetBytes(buff);
            return Convert.ToBase64String(buff);
        }

        public string Update(User updateObject)
        {
            return Connection.Query<string>(@"spUser_Upd @UserId, @FirstName, @LastName, @Email, 
                            @DepartId, @Phone, @IsFullAccess,
                            @IsPageReport, @IsPageAddress, @IsPageAssort, @IsPageMML, @IsPageSale, @IsPageUser, @IsPageSetup",
                new
                {
                    FirstName = updateObject.FirstName,
                    LastName = updateObject.LastName,
                    Email = updateObject.Email,
                    DepartId = updateObject.DepartId,
                    Phone = updateObject.Phone,
                    IsFullAccess = updateObject.IsFullAccess,
                    IsPageReport = updateObject.IsPageReport,
                    IsPageAddress = updateObject.IsPageAddress,
                    IsPageAssort = updateObject.IsPageAssort,
                    IsPageMML = updateObject.IsPageMML,
                    IsPageSale = updateObject.IsPageSale,
                    IsPageUser = updateObject.IsPageUser,
                    IsPageSetup = updateObject.IsPageSetup,
                    UserId = updateObject.Id

                }).Single(); 
        }
               

        public void Delete(int id)
        {
            try
            {
                Connection.Execute("spUser_Delete @Id", new { Id = id });
            }
            catch
            {
            }
        }

        public IEnumerable<Depart> DepartCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as DepartId, '(Все)' as DepartName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as DepartId, '(Выберите должность)' as DepartName, 0 as SortIndex union all ";
            strSQL += @"select DepartId, DepartName, 1 as SortIndex from [Depart] (nolock) order by SortIndex, DepartName";
            return Connection.Query<Depart>(strSQL);
        }
    }
}
